package br.com.ffm.praticas.dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Calendar;
import java.util.GregorianCalendar;
import java.util.LinkedList;
import java.util.List;

import br.com.ffm.praticas.bean.PersonType;
import br.com.ffm.praticas.bean.SingleCostumer;
import br.com.ffm.praticas.connection.ConnectionDB;
import br.com.ffm.praticas.constants.Constant;

public class SingleCostumerDAO {
	
	/**
	 * Method that return all the Single Costumers
	 * @return List<SingleCostumer>
	 * @throws SQLException
	 */
	@SuppressWarnings("deprecation")
	public List<SingleCostumer> getSingleCostumers() throws SQLException {
		String query = "SELECT * FROM person p INNER JOIN person_type pt ON ((p.person_type_id = pt.id) and (pt.description = 'Single Costumer'))";
		AddressDAO addressDAO = new AddressDAO();
		ContactDAO contactDAO = new ContactDAO();
		PersonTypeDAO personTypeDAO = new PersonTypeDAO();
		List<SingleCostumer> list = new LinkedList<SingleCostumer>();
		ResultSet result = ConnectionDB.getInstance().executeReader(query);
		while (result.next()) {
			SingleCostumer singleCostumer = new SingleCostumer();
			singleCostumer.setId(result.getInt("id"));
			singleCostumer.setContact(contactDAO.getContact(result.getInt("contact_id")));
			singleCostumer.setHomeAdress(addressDAO.getAddress(result.getInt("home_address_id")));
			singleCostumer.setPersonType(personTypeDAO.getPersonType(result.getInt("person_type_id")));
			if (result.getString("name") != null)
				singleCostumer.setName(result.getString("name"));
			if (result.getString("last_name") != null)
				singleCostumer.setLastName(result.getString("last_name"));
			if (result.getString("rg") != null)
				singleCostumer.setRg(result.getString("rg"));
			if (result.getString("cpf") != null)
				singleCostumer.setCpf(result.getString("cpf"));
			singleCostumer.setTotalBuy(result.getInt("total_buy"));
			if (result.getDate("birthday") != null) {
				int day = result.getDate("birthday").getDay();
				int month = result.getDate("birthday").getMonth();
				int year = result.getDate("birthday").getYear();
				GregorianCalendar birthday = new GregorianCalendar(year, month, day);
				singleCostumer.setBirthday(birthday);
			}
			list.add(singleCostumer);
		}
		ConnectionDB.getInstance().close();
		return list;
	}
	
	/**
	 * Method that returns a specific Single Costumer for your id
	 * @param id Single Costumer Identifier
	 * @return SingleCostumer
	 * @throws SQLException
	 */
	@SuppressWarnings("deprecation")
	public SingleCostumer getSingleCostumer(int id) throws SQLException {
		String query = "SELECT * FROM person p INNER JOIN person_type pt ON ((p.person_type_id = pt.id) and (pt.description = 'Single Costumer')) WHERE (p.id = " + id + " )";
		AddressDAO addressDAO = new AddressDAO();
		ContactDAO contactDAO = new ContactDAO();
		PersonTypeDAO personTypeDAO = new PersonTypeDAO();
		SingleCostumer singleCostumer;
		ResultSet result = ConnectionDB.getInstance().executeReader(query);
		if (result.next()) {
			 singleCostumer = new SingleCostumer();
			 singleCostumer.setId(result.getInt("id"));
			 singleCostumer.setContact(contactDAO.getContact(result.getInt("contact_id")));
			 singleCostumer.setHomeAdress(addressDAO.getAddress(result.getInt("home_address_id")));
			 singleCostumer.setPersonType(personTypeDAO.getPersonType(result.getInt("person_type_id")));
			 if (result.getString("name") != null)
				 singleCostumer.setName(result.getString("name"));
	 		 if (result.getString("last_name") != null)
 			 	 singleCostumer.setLastName(result.getString("last_name")); 
			 if (result.getString("rg") != null )
				 singleCostumer.setRg(result.getString("rg"));
			 if (result.getString("cpf") != null)
				 singleCostumer.setCpf(result.getString("cpf"));
			 singleCostumer.setTotalBuy(result.getInt("total_buy"));
			 if (result.getDate("birthday") != null) {
				 int day = result.getDate("birthday").getDay();
				 int month = result.getDate("birthday").getMonth();
				 int year = result.getDate("birthday").getYear();
				 GregorianCalendar birthday = new GregorianCalendar(year, month, day);
				 singleCostumer.setBirthday(birthday);
			 }
			 ConnectionDB.getInstance().close();
			 return singleCostumer;
		}
		ConnectionDB.getInstance().close();
		return null;
	}
	
	/**
	 * Method that returns a specific Single Costumer for your CPF
	 * @param cpf Single Costumer CPF
	 * @return SingleCostumer
	 * @throws SQLException
	 */
	@SuppressWarnings("deprecation")
	public SingleCostumer getSingleCostumer(String cpf) throws SQLException {
		String query = "SELECT * FROM person p INNER JOIN person_type pt ON ((p.person_type_id = pt.id) and (pt.description = 'Single Costumer')) WHERE (p.cpf = '" + cpf + "' )";
		AddressDAO addressDAO = new AddressDAO();
		ContactDAO contactDAO = new ContactDAO();
		PersonTypeDAO personTypeDAO = new PersonTypeDAO();
		SingleCostumer singleCostumer;
		ResultSet result = ConnectionDB.getInstance().executeReader(query);
		if (result.next()) {
			 singleCostumer = new SingleCostumer();
			 singleCostumer.setId(result.getInt("id"));
			 singleCostumer.setContact(contactDAO.getContact(result.getInt("contact_id")));
			 singleCostumer.setHomeAdress(addressDAO.getAddress(result.getInt("home_address_id")));
			 singleCostumer.setPersonType(personTypeDAO.getPersonType(result.getInt("person_type_id")));
			 if (result.getString("name") != null)
				 singleCostumer.setName(result.getString("name"));
			 if (result.getString("last_name") != null)
				 singleCostumer.setLastName(result.getString("last_name"));
			 if (result.getString("rg") != null)
				 singleCostumer.setRg(result.getString("rg"));
			 if (result.getString("cpf") != null)
				 singleCostumer.setCpf(result.getString("cpf"));
			 singleCostumer.setTotalBuy(result.getInt("total_buy"));
			 if (result.getDate("birthday") != null) {
				 int day = result.getDate("birthday").getDay();
				 int month = result.getDate("birthday").getMonth();
				 int year = result.getDate("birthday").getYear();
			 GregorianCalendar birthday = new GregorianCalendar(year, month, day);
			 singleCostumer.setBirthday(birthday);
			 }
			 ConnectionDB.getInstance().close();
	 		 return singleCostumer;
		}
		ConnectionDB.getInstance().close();
		return null;
	}
	
	/**
	 * Method that insert a Single Costumer
	 * @param SingleCostumer Single Costumer that will be inserted
	 * @return Single Costumer id
	 * @throws SQLException
	 */
	public int insert(SingleCostumer singleCostumer) throws SQLException {
		/* INSERT CONTACT */
		ContactDAO contactDAO = new ContactDAO();
		int contactId = contactDAO.insert(singleCostumer.getContact());
		
		/* INSERT ADDRES */
		AddressDAO addressDAO = new AddressDAO();
		int homeAddressId = addressDAO.insert(singleCostumer.getHomeAdress());
		
		/* INSERT SINGLE COSTUMER */
		PersonTypeDAO personTypeDAO = new PersonTypeDAO();
		PersonType personType = personTypeDAO.getPersonType(singleCostumer.getPersonType().getDescription());
		
		String query = "INSERT INTO person (contact_id, home_address_id, person_type_id, name, last_name, rg, cpf, birthday) ";
		query += "values (";
		query += contactId + ", ";
		query += homeAddressId + ", ";
		query += personType.getId() + ", ";
		query += "'" + singleCostumer.getName() + "', ";
		query += "'" + singleCostumer.getLastName() + "', ";
		query += "'" + singleCostumer.getRg() + "', ";
		query += "'" + singleCostumer.getCpf() + "', ";
		query += "'" + getDate(singleCostumer.getBirthday()) + "')";
		ConnectionDB.getInstance().executeCommand(query);
		
		query = "SELECT MAX(id) FROM person";
		int singleCostumerId = ConnectionDB.getInstance().executeScalar(query, Constant.SCALAR_COMMAND_MAX, "id");
		return singleCostumerId;
	}
	
	/**
	 * Method that delete a Single Costumer
	 * @param singleCostumer Single Costumer that will be deleted
	 * @return true it will have been deleted or false if it will not be deleted
	 */
	public boolean delete(SingleCostumer singleCostumer) {
		String query = "DELETE FROM person WHERE (id = " + singleCostumer.getId() + " )";
		try {
			ConnectionDB.getInstance().executeCommand(query);
			return true;
		} catch (SQLException sqlExcp) {
			sqlExcp.printStackTrace();
			return false;
		}
	}
	
	/**
	 * Method that update a Single Costumer
	 * @param singleCostumer Single Costumer that will be updated
	 * @return true it will have been updated or false if it will not be updated
	 */
	public boolean update(SingleCostumer singleCostumer) {
		/* UPDATE CONTACT */
		ContactDAO contactDAO = new ContactDAO();
		contactDAO.update(singleCostumer.getContact());
		
		/* UPDATE ADDRESSES */
		AddressDAO addressDAO = new AddressDAO();
		addressDAO.update(singleCostumer.getHomeAdress());
		
		/* UPDATE SINGLE COSTUMER */
		String query = "UPDATE person SET ";
		query += "contact_id = " + singleCostumer.getContact().getId() + ", ";
		query += "name = '" + singleCostumer.getName() + "', ";
		query += "last_name = '" + singleCostumer.getLastName() + "', ";
		query += "rg = '" + singleCostumer.getRg() + "', ";
		query += "cpf = '" + singleCostumer.getCpf() + "', ";
		//QUANTIDADE DE ITENS VENDIDOS COMPRADOS??
		query += "birthday = '" + getDate(singleCostumer.getBirthday()) + "' ";
		query += "WHERE (id = " + singleCostumer.getId() + " )";
		try {
			ConnectionDB.getInstance().executeCommand(query);
			return true;
		} catch (SQLException sqlExcp) {
			sqlExcp.printStackTrace();
			return false;
		}
	}
	
	/**
	 * Method that returns a Formated Date
	 * Egg: YYYY/MM/DD -> '2009/01/13'
	 * @param date GregorianCalendar that will be formated
	 * @return Formated Date String
	 */
	private String getDate(GregorianCalendar date) {
		String formatedDate = date.get(Calendar.YEAR) + "/" + (date.get(Calendar.MONTH) + 1) + "/" + date.get(Calendar.DAY_OF_MONTH);
		return formatedDate;
	}

}
